﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using SeizyUtils.Common.Data.Database.Parse;
using SeizyUtils;
using SeizyUtils.Binder;
using SeizyUtils.Common.Utilities;
using System.Text;

namespace SeizyUtils.Common.Data.Database
{
    class DatabaseManager : IDatabaseManager, IDatabaseManagerInternal
    {
        private readonly DBConfig _dbConfig;

        private SqlConnection _connection;

        private SqlTransaction _transaction;

        public DatabaseManager(DBConfig config)
        {
            _dbConfig = config;
        }

        public void connection()
        {
            if (_connection != null || _transaction != null)
            {
                throw new ApplicationException("前回のコネクションが正しく終了されていません。");
            }

            try
            {
                Console.Write("接続文字列：" + _dbConfig.getConnectionString(false));
                _connection = new SqlConnection(_dbConfig.getConnectionString(true));
                _connection.Open();
            }
            catch (SqlException e)
            {
                Console.Write(string.Format("DBへの接続に失敗しました。\n {0:s}", getSqlErrorMessage(e)));
                throw;
            }
            catch(Exception e)
            {
                Console.Write(string.Format("DBへの接続に失敗しました。\n {0:s}", e.StackTrace));
                throw;
            }
        }

        public void disConnect()
        {
            if (_connection != null)
            {
                _connection.Close();
                _connection = null;
            }
        }

        public void beginTransaction()
        {
            if (_connection == null)
            {
                throw new ApplicationException("getConnection()をbeginTransaction()より先に呼んでください。");
            }
            _transaction = _connection.BeginTransaction();
        }

        public void commit()
        {
            _transaction.Commit();
            _transaction = null;
        }

        public void rollback()
        {
            _transaction.Rollback();
            _transaction = null;
        }

        public IList<T> executeQuery<T>(IQuery query, QueryParam param, T bindedObject) where T : IBean
        {
            IList<IDBRecord> list = executeQuery(query, param);
            IList<T> results = new List<T>(list.Count);

            IDataBinder binder = new DataBinder();
            foreach (IDBRecord record in list)
            {
                T result = binder.bindObject(record, (T)bindedObject.Clone());
                results.Add(result);
            }
            return results;
        }

        public IList<IDBRecord> executeQuery(IQuery query, QueryParam param)
        {
            IList<IDBRecord> results = new List<IDBRecord>();
            using (SqlDataReader reader = doExecuteQuery(query, param))
            {
                if (reader == null)
                {
                    return results;
                }

                // カラム情報の取得
                ColumnModel model = new ColumnModel(createColumnModel(reader));

                try
                {
                    while (reader.Read())
                    {
                        IDBRecord record = new DBRecord(model);
                        results.Add(record);

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            IColumn column = DefinitionManager.getColumn(reader.GetName(i));
                            if (column == null)
                            {
                                Console.Write("カラム名[" + reader.GetName(i) + "]が見つかりませんでした。");
                                throw new ApplicationException("カラム名[" + reader.GetName(i) + "]が見つかりませんでした。");
                            }
                            object value = reader[column.getName()];

                            record.SetObject(column, value);
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.Write(e.GetType().Name + "" + e.StackTrace);
                }
            }
            return results;
        }

        private SqlDataReader doExecuteQuery(IQuery query, QueryParam param)
        {
            try
            {
                SqlCommand command = _connection.CreateCommand();
                command.Transaction = _transaction;
                command.CommandText = QueryParser.ParseQuery(query, param);
                Console.Write(command.CommandText);
                return command.ExecuteReader();
            }
            catch (SqlException e)
            {
                Console.Write(string.Format("検索に失敗しました。\n {0:s}", getSqlErrorMessage(e)));
                throw;
            }
            catch(Exception e)
            {
                Console.Write(string.Format("検索に失敗しました。\n {0:s}", e.StackTrace));
                throw;
            }
        }

        /// <summary>
        /// カラムモデルを生成します。
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private IList<string> createColumnModel(SqlDataReader reader)
        {
            IList<string> results = new List<string>();
            for(int i=0; i<reader.FieldCount; i++){
                results.Add(reader.GetName(i));
            }
            return results;
        }

        public int executeScalar(IQuery query, QueryParam param)
        {
            try
            {
                SqlCommand command = _connection.CreateCommand();
                command.Transaction = _transaction;
                command.CommandText = QueryParser.ParseQuery(query, param);
                Console.Write(command.CommandText);
                return (int)command.ExecuteScalar();
            }
            catch (SqlException e)
            {
                Console.Write(string.Format("検索に失敗しました。\n {0:s}", getSqlErrorMessage(e)));
                throw;
            }
            catch (Exception e)
            {
                Console.Write(string.Format("検索に失敗しました。\n {0:s}", e.StackTrace));
                throw;
            }
        }

        public int executeNonQuery(IQuery query, QueryParam param)
        {
            try
            {
                SqlCommand command = _connection.CreateCommand();
                command.Transaction = _transaction;
                command.CommandText = QueryParser.ParseQuery(query, param);
                Console.Write(command.CommandText);
                return (int) command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Write(string.Format("登録に失敗しました。\n {0:s}", getSqlErrorMessage(e)));
                throw;
            }
            catch (Exception e)
            {
                Console.Write(string.Format("登録に失敗しました。\n {0:s}", e.StackTrace));
                throw;
            }
        }

        public static string getSqlErrorMessage(SqlException ex)
        {
            StringBuilder errorMessages = new StringBuilder();
            errorMessages.Append(ex.StackTrace);
            errorMessages.Append("\n");
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                errorMessages.Append("Index #" + i + "\n" +
                    "Message: " + ex.Errors[i].Message + "\n" +
                    "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                    "Source: " + ex.Errors[i].Source + "\n" +
                    "Procedure: " + ex.Errors[i].Procedure + "\n");
            }
            return errorMessages.ToString();
        }

        #region IDatabaseManagerInternal メンバ

        public void executeNonQuery(string sql)
        {
            SqlCommand command = _connection.CreateCommand();
            command.Transaction = _transaction;
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }

        #endregion
    }

}
